<cfinclude template="#CFMapping_Admin#/Application.cfm">
<cfset adminTitle = "Spread the Word Report">

<cfparam name="filterStartDate" default="9/26/07">
<cfparam name="filterEndDate" default="#now()#">

<cfquery name="countSentEmails" datasource="#datasourcename#">
	select	count(STW_SPREADTHEWORDID) as total
	from	SPREAD_THE_WORD
	where	1 = 1
			<cfif len(filterStartDate)>
				and
					STW_DATECREATED >= <cfqueryparam value="#filterStartDate#" cfsqltype="CF_SQL_TIMESTAMP">
			</cfif>
			<cfif len(filterEndDate)>
				and
					STW_DATECREATED <= <cfqueryparam value="#dateAdd("l", -1, dateAdd("d", 1, filterEndDate))#" cfsqltype="CF_SQL_TIMESTAMP">
			</cfif>
</cfquery>

<cfquery name="getClickThroughCounts" datasource="#datasourcename#">
	select	count(STC_SPREADTHEWORDCLICKID) AS clicks, stc_navid, nav_name, nav_sortorder2
	from	SPREAD_THE_WORD,
			SPREAD_THE_WORD_CLICKS,
			navigation
	where	stw_spreadthewordid = stc_spreadthewordid
			<cfif len(filterStartDate)>
				and
					STW_DATECREATED >= <cfqueryparam value="#filterStartDate#" cfsqltype="CF_SQL_TIMESTAMP">
			</cfif>
			<cfif len(filterEndDate)>
				and
					STW_DATECREATED <= <cfqueryparam value="#dateAdd("l", -1, dateAdd("d", 1, filterEndDate))#" cfsqltype="CF_SQL_TIMESTAMP">
			</cfif>
			and
				nav_navigationid = stc_navid
	group by stc_navid, nav_name, nav_sortorder2
	order by	nav_sortorder2
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
	<cfoutput>
	<title>JelCMS<cfif Len(adminTitle) neq 0> - #adminTitle#</cfif></title>
	</cfoutput>
	<link rel="StyleSheet" href="/admin/styles/admin.css" type="text/css">
	<script language="JavaScript" src="/admin/scripts/base.js" type="text/javascript"></script>

</head>

<body background="/admin/images/background-dots.gif" onload="doLoadProc();">
<script language="JavaScript" src="/admin/scripts/dhtml.js" type="text/javascript"></script>
<link href="/admin/styles/dhtml.css" rel="stylesheet" type="text/css">
<div id="hideDhtml" onmouseover="hideLayer('hideDhtml'); delayedNavHide();">
    <a href="javascript:void(0);" onmouseover="hideLayer('hideDhtml'); delayedNavHide();"><img src="/admin/images/spacer.gif" width="99%" height="99%" border="0" alt="" galleryimg="no"></a>
</div>

<!--- ******************************************************** --->
<!--- Layout Top --->
<!--- ******************************************************** --->
<cfinclude template="#CFMapping_Admin#/layouts/layout-top-1col.cfm">

<!--- ******************************************************** --->
<!--- theView --->
<!--- ******************************************************** --->

<!--- outer formatting table --->
<table width="100%" border="0" cellspacing="0" cellpadding="8">
	<tr>
		<td>

			<cfoutput>
				
				<table width="100%" border="0" cellspacing="0" cellpadding="4" bgcolor="##E9E9E9">
					<tr>
						<td>
							<table border="0" cellspacing="0" cellpadding="4">
								<tr valign="top">
									<td colspan="2"><b>Filter By:</b><br></td>
								</tr>
								<cfform name="filterForm" action="#script_name#" method="post">
								<tr>
									<td>Start Date:<br></td>
									<td>
										<cfinput 	type="text"
													name="filterStartDate"
													value="#DateFormat(filterStartDate,'m/d/yyyy')#"
													message="Start Date must be in m/d/yyyy format."
													required="No"
													size="10"
													validate="date"
													class="formText"> (m/d/yyyy) <a href="" onclick="window.dateField = document.filterForm.filterStartDate;calendar = window.open('/admin/interfaces/forms/calendar.cfm','cal','width=270,height=250'); return false;"><img src="/admin/images/calendar.gif" width=16 height=16 alt="" border="0"></a><br>
									</td>
								</tr>
								<tr>
									<td>End Date:<br></td>
									<td>
										<cfinput 	type="text"
													name="filterEndDate"
													value="#DateFormat(filterEndDate,'m/d/yyyy')#"
													message="End Date must be in m/d/yyyy format."
													required="No"
													size="10"
													validate="date"
													class="formText"> (m/d/yyyy) <a href="" onclick="window.dateField = document.filterForm.filterEndDate;calendar = window.open('/admin/interfaces/forms/calendar.cfm','cal','width=270,height=250'); return false;"><img src="/admin/images/calendar.gif" width=16 height=16 alt="" border="0"></a><br>
									</td>
								</tr>
								<tr>
									<td colspan="2"><input type="submit" value="&nbsp;Update&nbsp;" class="formButton"><br></td>
								</tr>
								</cfform>
							</table>
						</td>
					</tr>
				</table>


				<cfif countSentEmails.total>
					<p>
						<strong>Total emails sent:</strong> #countSentEmails.total#
					</p>
					<cfif getClickThroughCounts.recordCount>
						
						<table width="100%" border="1" cellspacing="0" cellpadding="4" bordercolor="##e9e9e9" rules="rows" frame="void">
							<tr class="header3">
								<td>navID<br></td>
								<td>Name<br></td>
								<td nowrap>Click-Thrus<br></td>
							</tr>
							<cfloop query="getClickThroughCounts">
								<tr class="data3">
									<td align="right" width="40">#stc_navid#</td>
									<td>
										<a href="/index.cfm?navid=#stc_navid#">#nav_name#</a>
									<!--- <cfloop index="i" from="2" to="#nav_level#">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</cfloop><a href="/admin/?theAdmin=siteEditor&theView=detailNavigation&navigationID=#nav_navigationid#"<cfif NOT nav_isvisible>  class="invisibleNavigation"</cfif>>#nav_name#</a><br> --->
									</td>
									<td nowrap>#clicks#<br></td>
								</tr>
							</cfloop>
						</table>
					<cfelse>
						No clicks have been recorded for any emails sent within the selected filter range.
					</cfif>
				<cfelse>
					No emails have been sent in the selected filter range.
				</cfif>
				
			</cfoutput>

		</td>
	</tr>
</table>
<!--- outer formatting table --->
<!--- ******************************************************** --->

<!--- ******************************************************** --->
<!--- Layout Bottom --->
<!--- ******************************************************** --->
<cfinclude template="#CFMapping_Admin#/layouts/layout-bottom-1col.cfm">


</body>
</html>